<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE topic
  PUBLIC "-//OASIS//DTD DITA Composite//EN" "ditabase.dtd">
<topic id="topic1">
  <title id="jr20941">gpcheckcat</title>
  <body>
    <p>The <codeph>gpcheckcat</codeph> utility tests Greenplum Database catalog tables for
      inconsistencies. </p>
    <p>The utility is in <codeph>$GPHOME/bin/lib</codeph>.</p>
    <section id="section2">
      <title>Synopsis</title>
      <codeblock>gpcheckcat [ &lt;options&lt;] [ &lt;dbname>] 

  Options:
     -g &lt;dir>
     -p &lt;port>
     -s &lt;test_name | 'test_name1, test_name2 [, ...]'>  
     -P &lt;password>
     -U &lt;user_name>
     -S {none | only}
     -O
     -R &lt;test_name | 'test_name1, test_name2 [, ...]'>
     -C &lt;catalog_name>
     -B &lt;parallel_processes>
     -v
     -A

gpcheckcat  -l 

gpcheckcat -? | --help 
</codeblock>
    </section>
    <section id="section3">
      <title>Description</title>
      <p>The <codeph>gpcheckcat</codeph> utility runs multiple tests that check for database catalog
        inconsistencies. Some of the tests cannot be run concurrently with other workload statements
        or the results will not be usable. Restart the database in restricted mode when running
          <codeph>gpcheckcat</codeph>, otherwise <codeph>gpcheckcat</codeph> might report
        inconsistencies due to ongoing database operations rather than the actual number of
        inconsistencies. If you run <codeph>gpcheckcat</codeph> without stopping database activity,
        run it with <codeph>-O</codeph> option. </p>
      <note>Any time you run the utility, it checks for and deletes orphaned, temporary database
        schemas (temporary schemas without a session ID) in the specified databases. The utility
        displays the results of the orphaned, temporary schema check on the command line and also
        logs the results.</note>
      <p>Catalog inconsistencies are inconsistencies that occur between Greenplum Database system
        tables. In general, there are three types of inconsistencies:<ul id="ul_jry_gmh_mp">
          <li>Inconsistencies in system tables at the segment level. For example, an inconsistency
            between a system table that contains table data and a system table that contains column
            data. As another, a system table that contains duplicates in a column that should to be
            unique.</li>
        </ul><ul id="ul_smw_hmh_mp">
          <li>Inconsistencies between same system table across segments. For example, a system table
            is missing row on one segment, but other segments have this row. As another example, the
            values of specific row column data are different across segments, such as table owner or
            table access privileges.</li>
          <li>Inconsistency between a catalog table and the filesystem. For example, a file exists
            in database directory, but there is no entry for it in the pg_class table.</li>
        </ul></p>
    </section>
    <section id="section4">
      <title>Options</title>
      <parml>
        <plentry>
          <pt>-A</pt>
          <pd>Run <codeph>gpcheckcat</codeph> on all databases in the Greenplum Database
            installation.</pd>
        </plentry>
        <plentry>
          <pt>-B &lt;parallel_processes></pt>
          <pd>The number of processes to run in parallel. </pd>
          <pd>
            <p>The <codeph>gpcheckcat</codeph> utility attempts to determine the number of
              simultaneous processes (the batch size) to use. The utility assumes it can use a
              buffer with a minimum of 20MB for each process. The maximum number of parallel
              processes is the number of Greenplum Database segment instances. The utility displays
              the number of parallel processes that it uses when it starts checking the catalog.
              <note>The utility might run out of memory if the number of errors returned exceeds the
                buffer size. If an out of memory error occurs, you can lower the batch size with the
                  <codeph>-B</codeph> option. For example, if the utility displays a batch size of
                936 and runs out of memory, you can specify <codeph>-B 468</codeph> to run 468
                processes in parallel. </note></p>
          </pd>
        </plentry>
        <plentry>
          <pt>-C <varname>catalog_table</varname></pt>
          <pd>Run cross consistency, foreign key, and ACL tests for the specified catalog
            table.</pd>
        </plentry>
        <plentry>
          <pt>-g <varname>data_directory</varname></pt>
          <pd>Generate SQL scripts to fix catalog inconsistencies. The scripts are placed in
              <varname>data_directory</varname>.</pd>
        </plentry>
        <plentry>
          <pt>-l</pt>
          <pd>List the <codeph>gpcheckcat</codeph> tests.</pd>
        </plentry>
        <plentry>
          <pt>-O</pt>
          <pd>Run only the <codeph>gpcheckcat</codeph> tests that can be run in online (not
            restricted) mode. </pd>
        </plentry>
        <plentry>
          <pt>-p <varname>port</varname></pt>
          <pd>This option specifies the port that is used by the Greenplum Database.</pd>
        </plentry>
        <plentry>
          <pt>-P <varname>password</varname></pt>
          <pd>The password of the user connecting to Greenplum Database.</pd>
        </plentry>
        <plentry>
          <pt>-R <varname>test_name | 'test_name1,test_name2 [, ...]'</varname>
          </pt>
          <pd>Specify one or more tests to run. Specify multiple tests as a comma-delimited list of
            test names enclosed in quotes. </pd>
          <pd>Some tests can be run only when Greenplum Database is in restricted mode.</pd>
          <pd>These are the tests that can be performed: <p><codeph>acl</codeph> - Cross consistency
              check for access control privileges</p>
            <p><codeph>aoseg_table</codeph> - Check that the vertical partition
              information (vpinfo) on segment instances is consistent
              with <codeph>pg_attribute</codeph> (checks only append-optimized,
              column storage tables in the database)</p>
            <p><codeph>duplicate</codeph> - Check for duplicate entries</p>
            <p><codeph>foreign_key</codeph> - Check foreign keys</p>
            <p><codeph>inconsistent</codeph> - Cross consistency check for master segment
              inconsistency</p>
            <p><codeph>missing_extraneous</codeph> - Cross consistency check for missing or
              extraneous entries</p>
            <p><codeph>owner</codeph> - Check table ownership that is inconsistent with the master
              database</p>

            <p><codeph>orphaned_toast_tables</codeph> - Check for orphaned TOAST tables. 
              <note>There are several ways a TOAST table can become orphaned where a repair script
                cannot be generated and a manual catalog change is required. One way is if the
                  <codeph>reltoastrelid</codeph> entry in <i>pg_class</i> points to an incorrect
                TOAST table (a TOAST table mismatch). Another way is if both the
                  <codeph>reltoastrelid</codeph> in <i>pg_class</i> is missing and the
                  <codeph>pg_depend</codeph> entry is missing (a double orphan TOAST table). If a
                manual catalog change is needed, <codeph>gpcheckcat</codeph> will display detailed
                steps you can follow to update the catalog. <ph otherprops="pivotal">Contact VMware
                  Support if you need help with the catalog change.</ph></note></p>
            <p><codeph>part_integrity</codeph> - Check <i>pg_partition</i> branch integrity,
              partition with OIDs, partition distribution policy</p>
            <p><codeph>part_constraint</codeph> - Check constraints on partitioned tables</p>
            <p><codeph>unique_index_violation</codeph> - Check tables that have columns with the
              unique index constraint for duplicate entries</p>
            <p><codeph>dependency</codeph> - Check for dependency on non-existent objects
              (restricted mode only)</p>
            <p><codeph>distribution_policy</codeph> - Check constraints on randomly distributed
              tables (restricted mode only)</p>
            <p><codeph>namespace</codeph> - Check for schemas with a missing schema definition
              (restricted mode only)</p>
            <p><codeph>pgclass</codeph> - Check <i>pg_class</i> entry that does not have any
              corresponding <i>pg_attribute</i> entry (restricted mode only)</p>
          </pd>
        </plentry>
        <plentry>
          <pt>-s <codeph>test_name | 'test_name1, test_name2 [, ...]'</codeph></pt>
          <pd>Specify one ore more tests to skip. Specify multiple tests as a comma-delimited list
            of test names enclosed in quotes.</pd>
        </plentry>
        <plentry>
          <pt>-S {none | only}</pt>
          <pd>Specify this option to control the testing of catalog tables that are shared across
            all databases in the Greenplum Database installation, such as <i>pg_database</i>. </pd>
          <pd>The value <codeph>none</codeph> disables testing of shared catalog tables. The value
              <codeph>only</codeph> tests only the shared catalog tables. </pd>
        </plentry>
        <plentry>
          <pt>-U <varname>user_name</varname></pt>
          <pd>The user connecting to Greenplum Database.</pd>
        </plentry>
        <plentry>
          <pt>-? | --help</pt>
          <pd>Displays the online help.</pd>
        </plentry>
        <plentry>
          <pt>-v (verbose)</pt>
          <pd>Displays detailed information about the tests that are performed.</pd>
        </plentry>
      </parml>
    </section>
    <section><title>Notes</title>The utility identifies tables with missing attributes and displays
      them in various locations in the output and in a non-standardized format. The utility also
      displays a summary list of tables with missing attributes in the format <codeph>&lt;database>.
        &lt;schema>. &lt;table>. &lt;segment_id></codeph> after the output information is
        displayed.<p>If <codeph>gpcheckcat</codeph> detects inconsistent OID (Object ID)
        information, it generates one or more verification files that contain an SQL query. You can
        run the SQL query to see details about the OID inconsistencies and investigate the
        inconsistencies. The files are generated in the directory where <codeph>gpcheckcat</codeph>
        is invoked. </p><p>This is the format of the
        file:</p><pre>gpcheckcat.verify.<varname>dbname</varname>.<varname>catalog_table_name</varname>.<varname>test_name</varname>.<varname>TIMESTAMP</varname>.sql</pre><p>This
        is an example verification filename created by <codeph>gpcheckcat</codeph> when it detects
        inconsistent OID (Object ID) information in the catalog table <i>pg_type</i> in the database
          <codeph>mydb</codeph>:<codeblock>gpcheckcat.verify.mydb.pg_type.missing_extraneous.20150420102715.sql</codeblock></p><p>This
        is an example query from a verification
      file:</p><pre>SELECT *
  FROM (
       SELECT relname, oid FROM pg_class WHERE reltype 
         IN (1305822,1301043,1301069,1301095)
       UNION ALL
       SELECT relname, oid FROM gp_dist_random('pg_class') WHERE reltype 
         IN (1305822,1301043,1301069,1301095)
       ) alltyprelids
  GROUP BY relname, oid ORDER BY count(*) desc ;</pre></section>
  </body>
</topic>
